<?php

 class ReportsUtility
 {
	
	
	public static function sendEmail($emailInfo, $attachments = array())
	{
		Zend_loader::loadClass("Zend_Mail_Transport_Smtp");
		Zend_loader::loadClass("Zend_Mail");
		Zend_loader::loadClass("Zend_Mime_Part");
		Zend_loader::loadClass("Zend_Mime");
		Zend_loader::loadClass("Zend_Session_Namespace");
		Zend_loader::loadClass("CodesUtility");
		
		$subject = $emailInfo->subject;
		$body = $emailInfo->body;
		$toEmail = $emailInfo->to;
		$fromEmail = $emailInfo->from;
		
		if (isset($emailInfo->cc)){
			$ccEmail = $emailInfo->cc;	
		}
		
		if (isset($emailInfo->cc)){
			$bccEmail = $emailInfo->bcc;	
		}
		
		$body = urldecode($body);	
		
		$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
		$configFile = $client_session->config;
		//'mail.dwebsite.com'
		$transport = new Zend_Mail_Transport_Smtp($configFile->email->transport,array('port' => CodesUtility::SMTP_PORT));
        Zend_Mail::setDefaultTransport($transport);
		
        $mailer = new Zend_Mail();
        $mailer->setBodyHTML($body);
        $mailer->setFrom($fromEmail);        
        $mailer->setSubject($subject);
        
        $toEmailArray = explode(";",$toEmail);
        foreach ($toEmailArray as $toEmailItem){
        	$mailer->addTo($toEmailItem);	
        }
        
        if (isset($ccEmail)){
        	$ccEmailArray = explode(";",$ccEmail);
	        foreach ($ccEmailArray as $ccEmailItem){
	        	$mailer->addCc($ccEmailItem);	
	        }	
        }
        
        if (isset($bccEmail)){
	        $bccEmailArray = explode(";",$bccEmail);
	        foreach ($bccEmailArray as $bccEmailItem){
	        	$mailer->addBcc($bccEmailItem);	
	        }
        }
        
        foreach ($attachments as $attach){
        	$at = $mailer->createAttachment($attach->content);
        	
        	if (isset($attach->type)){
        		$at->type = $attach->type;
        		//$at->type = (isset($attach->type)) ? ($attach->type) : Zend_Mime::TYPE_OCTETSTREAM;
        	}
        	
        	if (isset($attach->disposition)){
        		$at->disposition = $attach->disposition;
        		//$at->disposition = (isset($attach->disposition)) ? ($attach->disposition) : Zend_Mime::DISPOSITION_ATTACHMENT;	
        	}
			
			if (isset($attach->encoding)){
				$at->encoding = $attach->encoding;
				//$at->encoding    = (isset($attach->encoding)) ? ($attach->encoding) : Zend_Mime::ENCODING_8BIT;	
			}
			
			$at->filename    = $attach->filename;
			
			//$mailer->addAttachment($at);
        }
        
		$mailer->send();			
	}
	
	public static function generateCsv($header = array(), $data = array(), $separator = ',') 
	{
		Zend_loader::loadClass("CSVUtil");
		
		$csv = new CSVUtil();
		$txt = $csv->arrayToCsvString($data, $separator, $trim='none', $removeEmptyLines=TRUE);
		$header = $csv->arrayToCsvString($header , $separator, $trim='none', $removeEmptyLines=TRUE);
		$csvContent = $header."\n".$txt;
		return $csvContent;
	}
	
	public static function generateTdf($header = array(), $data = array())
	{
		$tdfContent = "";
		$tdfContent .= ( join("\t",$header) . "\n" );
		foreach ($data as $record){
			$tdfContent .= ( join("\t",$record) . "\n" );
		}
		return $tdfContent;
	}
	
	public static function generateXls($header = array(), $data = array())
	{		
		Zend_loader::loadClass("CADUtility");
						
		$cadutil = new CADUtility();
		Zend_Loader::loadClass("Zend_Session_Namespace");
		$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
		
		$tempFileName = $client_session->CLIENT_DIR.$client_session->config->paths->tmp."/".$cadutil->createGUID();
		
				
		ReportsUtility::generateXlsFile($tempFileName, $header, $data);						
				
		$xlsContent = file_get_contents($tempFileName);
			
		unset($tempFileName);
		
		return $xlsContent;
	}
	
	public static function generateXlsFile($filename, $header = array(), $data = array())
	{
		Zend_loader::loadClass("ExcelWriter");
								
		$excelWriter = new ExcelWriter($filename);
						
		$excelWriter->writeLine($header);
		foreach ($data as $record){
			$excelWriter->writeLine($record);
		}
		$excelWriter->close();				
	}
	
	public static function export($data, $filename, $contenttype) 
	{
		header("Content-type: $contenttype");
    	header("Content-Disposition: attachment; filename=$filename");
    	header("Expires: 0");
    	header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
    	header("Pragma: public");
   		echo $data;
   		exit;
	}
	
	public static function exportReport($exportData, $exportHeader, $exportType = 'csv')
	{
		Zend_loader::loadClass("Zend_Mime");
		
		if ($exportType == 'csv'){
			
			$dataToExport = ReportsUtility::generateCsv($exportHeader, $exportData, $separator = ',');
			$filename = "export.csv";
			$contentType = Zend_Mime::TYPE_TEXT;
			
		}else if ($exportType == 'xls'){
			
			$dataToExport = ReportsUtility::generateXls($exportHeader, $exportData);
			$filename = "export.xls";
			//$contentType = "application/vnd.ms-excel";					
			$contentType= "application/octet-stream";
		}
		
		ReportsUtility::export($dataToExport, $filename, $contentType);
	}
	
	
	public function catchSummary($param) {
		
		//print_r($param);
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("Fisher");
		Zend_Loader::loadClass("FishingArea");
		Zend_Loader::loadClass("OrganizationOrganism");
		Zend_Loader::loadClass("Organism");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("Receipt");
		Zend_Loader::loadClass("Tac");
		
		
		
		$query = new QueryCreator();
		
		$query->addSelect(" ifnull(farea.fishing_area_name,a.name) as FMA,concat(fisher.last_name,',',fisher.first_name) as fishersName ");
		$query->addSelect(" moid,organism.scientific_name,organism.name as common_name");
		
		$query->addSelect(" sum(received_quantity) as caught,sum(doa_quantity) as doa, sum(reject_quantity) as reject");
		$query->addSelect(" month(rcpt.date_receipt) as mymonth, year(rcpt.date_receipt) as myyear");
		$query->addSelect(" (sum(received_quantity)/tac.limit_quantity) * 100 as tacpercent");
		$query->addSelect(" if(sum(received_quantity) >= tac.limit_quantity,1,0) as taclimit ");
		$query->addFrom(Organization::_tablename . "  a  ");
		$query->addJoin(" LEFT JOIN ". Address::_tablename . " addr on addr.address_id = a.primary_address_id ");
		$query->addJoin(" LEFT JOIN ". FishingArea::_tablename . " farea on farea.org_id = a.org_id ");
		$query->addJoin(" LEFT JOIN ". Fisher::_tablename . " fisher on fisher.fishing_area_id = farea.fishing_area_id " );
		$query->addJoin(" LEFT JOIN ". OrganizationOrganism::_tablename . " orgorg on orgorg.org_id = a.org_id ");
		$query->addJoin(" LEFT JOIN ". Organism::_tablename . " organism on organism.organism_id = orgorg.organism_id ");
		$query->addJoin(" LEFT JOIN ". Receipt::_tablename . " rcpt on rcpt.supplier_org_id = a.org_id and rcpt.org_organism_id=orgorg.org_organism_id and rcpt.supplier_fisher_id = fisher.fisher_id ");
		$query->addJoin(" LEFT JOIN ". Tac::_tablename . " tac on tac.organism_id = organism.organism_id and farea.fishing_area_id = tac.fishing_area_id and rcpt.date_receipt between tac.date_start and tac.date_expire");
		
		if(isset($param['date_from']) && strlen($param['date_from']) > 0 ) 
			$query->addWhere(" date_format(rcpt.date_receipt,'%Y-%m-%d') between '". $param['date_from'] . "' and '". $param['date_to'] . "'");
		
		if(isset($param['country']) && strlen($param['country']) > 0)
			$query->addWhere(" addr.country_code = '". $param['country'] . "'" );
		
		if(isset($param['fma']) && strlen($param['fma']) > 0)
			$query->addWhere(" a.org_id = ". $param['fma'] );
		
		$query->addGroupBy(" a.org_id,farea.fishing_area_id,fisher.fisher_id,organism.moid,date_format(rcpt.date_receipt,'%Y%m') ");
		
		$query->addHaving(" caught > 0");
		
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		
		return $result;
	}
	
	public function OrsSummary($org_type,$param){
	
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("purchaseOrder");
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Receipt");
		Zend_Loader::loadClass("Address");
	
		$query = new QueryCreator();
	
		$query->addSelect(" po.po_id,a.name,date_format(date_order,'%Y-%m') as orderDate,date_format(date_receipt,'%Y-%m') as receiptDate ");
		$query->addSelect(" order_quantity as orderQty,sum(received_quantity - doa_quantity - reject_quantity ) as rvdQty");
		$query->addSelect(" monthname(date_order) as morder,monthname(date_receipt) as mreceipt,year(date_order) as yOrder,year(date_receipt) as yReceipt");
		$query->addFrom(Organization::_tablename . " a  ");
		$query->addJoin(" LEFT JOIN ". purchaseOrder::_tablename . " po on po.org_id = a.org_id " );
		$query->addJoin(" LEFT JOIN ". purchaseOrderDetail::_tablename . " podetail on podetail.po_id = po.po_id ");
		$query->addJoin(" LEFT JOIN ". Receipt::_tablename . " rcpt on rcpt.org_id =  a.org_id and rcpt.reference_id = podetail.po_detail_id ");
		$query->addJoin(" LEFT JOIN ". Address::_tablename . " addr on addr.address_id = a.primary_address_id ");
		$query->addWhere(" org_type = '". $org_type . "'");
		if(isset($param['date_form'])  && strlen($param['date_form']) > 0 )
			$query->addWhere(" date_order between '".  $param['date_from'] . "' and '". $param['date_to'] . "'" );
		if($param['country'])
			$query->addWhere(" country_code = '". $param['country'] ."'" );
		
		if($param['exporter'])
			$query->addWhere(" and a.org_id = ". $param['exporter']);
			
		$query->addWhere(" order_quantity > 0");
			
		$query->addGroupBy(" a.org_id,po.po_id,orderDate,receiptDate");
		
		$query->addOrderBy(" a.org_id,yOrder,yReceipt,po.po_id ");
		
		$db = DBCon::instance();
		
		$result = $db->executeQuery($query->createSQL());
		$head = array();
		$data = array();
		foreach($result as $key => $value){
			$yOrder = 0;
			if(!isset($data[$value['name']])) {
				$data[$value['name']] = array();
			}
				if(!isset($data[$value['name']]['month'][$value['yOrder']][$value['morder']]['order'][$value['po_id']])) {
					$yOrder = $value['orderQty'];
				} else {
					$yOrder = $data[$value['name']]['month'][$value['yOrder']][$value['morder']]['order'][$value['po_id']];
				}
					
					
				$data[$value['name']]['month'][$value['yOrder']][$value['morder']]['order'][$value['po_id']] = $yOrder;
				if($value['rvdQty'] > 0) {
					$data[$value['name']]['month'][$value['yReceipt']][$value['mreceipt']]['delivered'] = $value['rvdQty'];
					$head[$value['yReceipt']]['month'][$value['mreceipt']] = $value['mreceipt'];
				}
					
				$head[$value['yOrder']]['month'][$value['morder']] = $value['morder'];
		} 
		//$myarray['head'] = $head;
		$myarray[] = $head;
		$myarray[] = $data;
			
		return $myarray;
		
	}
	
	
	
	
	public function exportoexcelors($items,$fields) {
		
		
		
		Zend_Loader::loadClass("ExcelWriter");
		
		$filename = mt_rand();
		$excelwriter = new ExcelWriter();
		$excelwriter->ExcelWriter($filename.".xls");
		
		$excelwriter->writeRow();
		foreach( $fields as $key => $value ) {
			$excelwriter->writeCol($value);
		}
		$excelwriter->writeRow();
		foreach( $items as $itemkey => $data) {
			foreach ( $fields as $key => $fkey) {
					$excelwriter->writeCol($data[$key]);
			}
			$excelwriter->writeRow();
		}
		
		return $filename;
		
		
	}
	
	
	
	
	
}
?>